import pkgutil
import sqlite3


def create_table():
    conn = sqlite3.connect('test.db')
    c = conn.cursor()
    print(c.description)
    # create tables
    c.execute('''CREATE TABLE category (id int primary key, sort int, name text)''')
    c.execute('''CREATE TABLE book
          (id int primary key, 
           sort int, 
           name text, 
           price real, 
           category int,
           FOREIGN KEY (category) REFERENCES category(id))''')

    # save the changes
    conn.commit()
    # close the connection with the database
    conn.close()


def insert_table():
    conn = sqlite3.connect("test.db")
    c = conn.cursor()

    books = [(1, 1, 'Cook Recipe', 3.12, 1),
             (2, 3, 'Python Intro', 17.5, 2),
             (3, 2, 'OS Intro', 13.6, 2),
             ]

    # execute "INSERT"
    c.execute("INSERT INTO category VALUES (1, 1, 'kitchen')")

    # using the placeholder
    c.executemany("INSERT INTO category VALUES (?, ?, ?)", [(2, 2, 'computer')])

    # execute multiple commands
    c.executemany('INSERT INTO book VALUES (?, ?, ?, ?, ?)', books)

    conn.commit()
    conn.close()


def select_atble():
    conn = sqlite3.connect("test.db")
    c = conn.cursor()
    data = c.execute(""" SELECT * FROM category  """).fetchall()
    print(data)
    conn.close()


def read_package_data():
    data = pkgutil.get_data("test.sql", "test.db")
    print(str(data))
    # dirs = pkgutil.walk_packages(path='test', prefix='', onerror=None)
    # for dir in dirs:
    #     print(dir)

    # dirs = list(pkgutil.iter_modules(path=None, prefix=''))
    # print(dirs[0])
    # for dir in dirs:
    #     print(dir)


def test_conn():
    # import sqlite3
    connection = sqlite3.connect(':memory:')

    with connection:
        connection.execute(
            'CREATE TABLE events(ts, msg, PRIMARY KEY(ts, msg))')

    try:
        with connection:
            connection.executemany('INSERT INTO events VALUES (?, ?)', [
                (1, 'foo'),
                (2, 'bar'),
                (3, 'baz'),
                # (1, 'foo'),
            ])
    except (sqlite3.OperationalError, sqlite3.IntegrityError) as e:
        print('Could not complete operation:', e)

    # No row was inserted because transaction failed
    for row in connection.execute('SELECT * FROM events'):
        print(row)

    connection.close()


if __name__ == "__main__":
    test_conn()
